﻿CREATE PROCEDURE [dbo].[Report_CategorySameStoreComparison]
				@RangeReviewId int
	, @CategoryId smallint	
	, @StoreId smallint
	, @StoreGroupId smallint
	, @Delist bit
AS

declare @startDatePY DATE = 
	(
		select CASE WHEN HasPriorYear = 1 THEN DATEADD(Month, -12, FirstMonth) ELSE FirstMonth END
		from RangeReviews where Id = @RangeReviewId
	)
declare @startDate DATE = (select FirstMonth from RangeReviews where Id = @RangeReviewId)
declare @totalMonths INT = (select TotalMonths from RangeReviews where Id = @RangeReviewId)
declare @endDate DATE = DATEADD(Month, @totalMonths - 1, @startDate)
declare @salesTable TABLE (Title VARCHAR(200), CategoryId SMALLINT, Category VARCHAR(500), Date DATE, ExchangeRate MONEY, Units BIGINT, Sales MONEY, GM MONEY, PIDiff MONEY, ChargeOffs MONEY, InventoryCost MONEY, ArticleId INT, IsPriorYear BIT)  
declare @totalCoverage INT = 
(
	select 
		CASE 
			WHEN @StoreId <> 0 THEN 1 * @totalMonths
			WHEN @StoreGroupId <> 0 THEN (Select COUNT(StoreId) From StoreGroupMembers Where StoreGroupId = @StoreGroupId) * @totalMonths
			ELSE (Select Count(StoreId) from RangeReviewStores where RangeReviewId = @RangeReviewId) * @totalMonths
		END
)

INSERT INTO @salesTable (Title, CategoryId, Category, Date, ExchangeRate, Units, Sales, GM, PIDiff, ChargeOffs, InventoryCost, ArticleId, IsPriorYear)
SELECT
	rr.Title
	, cat.ParentId as CategoryId
	, parentCat.Name as Category
	, sales.Date
	, ex.Rate	
	, CASE WHEN umc.Id IS NOT NULL THEN SUM(Units / umc.Value) ELSE SUM(Units) END AS Units
	, CASE WHEN co.IsVATIncluded = 0 THEN SUM(Sales)  ELSE SUM(Sales / (1 + ar.TaxRate)) END AS Sales	
	, CASE WHEN am.Margin is not null THEN SUM(Sales) * am.Margin 
		   WHEN scm.Margin is not null THEN SUM(Sales) * scm.Margin 
		   WHEN cm.Margin is not null THEN SUM(Sales) * cm.Margin 
		   ELSE SUM(GM)END AS GM					
	, SUM(PIDiff) as PIDiff
	, SUM(ChargeOffs) as ChargeOffs
	, SUM(inv.Cost) as InventoryCost
	, ar.Id
	, CASE 
		WHEN sales.Date BETWEEN @startDatePY AND DATEADD(MONTH, @totalMonths - 1, @startDatePY) THEN 1
		WHEN sales.Date BETWEEN @startDate AND @endDate THEN 0
		ELSE NULL
	END AS IsPriorYear
FROM ArticleSales sales	
	INNER JOIN Articles ar on sales.ArticleId = ar.Id and ar.RangeReviewId = @RangeReviewId and ar.IsDeletedPerformance = 0	
	INNER JOIN Categories cat on ar.CategoryId = cat.Id
	INNER JOIN Categories parentCat on cat.ParentId = parentCat.Id and parentCat.ParentId = 6
	INNER JOIN RangeReviews rr on ar.RangeReviewId = rr.Id
	INNER JOIN Countries co on co.Id = rr.CountryId
	INNER JOIN Currencies cur on rr.CountryId = cur.CountryId
	LEFT JOIN CurrencyExchangeRates ex on ex.Date = sales.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
	LEFT JOIN UnitOfMeasureConvertions umc on ar.UnitOfMeasureConvertionId = umc.Id and ar.UOM = umc.SourceUnit
	LEFT JOIN TheoreticalMargins am  on am.CountryId = rr.CountryId AND  am.ArticleId = ar.Id  
	LEFT JOIN TheoreticalMargins scm on scm.CountryId = rr.CountryId AND (scm.ArticleId is null AND scm.CategoryId = cat.Id) 
	LEFT JOIN TheoreticalMargins cm  on cm.CountryId = rr.CountryId AND (cm.ArticleId is null AND cm.CategoryId <> cat.Id AND cm.CategoryId = cat.ParentId)
	LEFT JOIN Inventories inv on inv.ArticleId = sales.ArticleId and inv.Date = sales.Date and inv.StoreId = sales.StoreId	
WHERE
	(sales.Date BETWEEN @startDatePY AND @endDate)
AND sales.Confirmed = 1		
AND sales.StoreId in (select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId)
AND @Delist is null OR ar.Delist = @Delist
AND (@StoreId = 0 OR (@StoreId <> 0 AND sales.StoreId = @StoreId))
AND (@StoreGroupId = 0 OR (@StoreGroupId <> 0 AND sales.StoreId IN (Select StoreId From StoreGroupMembers Where StoreGroupId = @StoreGroupId)))
GROUP BY rr.Title, cat.ParentId, parentCat.Name, ar.Id, ar.Name, ar.TaxRate, am.Margin, scm.Margin, cm.Margin, umc.Value, umc.TargetUnit, umc.Id, co.IsVATIncluded, sales.Date, ex.Rate
OPTION(RECOMPILE);

Select 
	CASE 
		WHEN @StoreId <> 0 THEN (Select Name From Stores Where Id = @StoreId) 
		WHEN @StoreGroupId <> 0 THEN (Select Name From StoreGroups Where Id = @StoreGroupId) 
		ELSE 'General'
	END AS Name 
	, Title
	, CategoryId
	, Category
	, SUM(s.Units) / CAST(@totalCoverage AS MONEY) as Units
	, SUM(s.Sales * s.ExchangeRate) / @totalCoverage as Sales
	, SUM(s.GM * s.ExchangeRate) / @totalCoverage as GM 
	, CAST(SUM(s.Units) AS MONEY) / Total.Units as UnitsShare
	, SUM(s.Sales) / Total.Sales as SalesShare
	, SUM(s.GM) / Total.GM as GMShare
	, SUM(PIDiff) / SUM(s.Sales) as PIDiff
	, SUM(ChargeOffs) / SUM(s.Sales) as ChargeOffs
	, SUM(s.InventoryCost * s.ExchangeRate) / @totalCoverage as InventoryCost
	, Spaces.LinealSpace as LinearSpace
	, COUNT(distinct ArticleId) SKUs
	, s.IsPriorYear 
From @salesTable s
Inner Join 
(
	Select 
		SUM(Units) as Units, 
		SUM(Sales) as Sales, 
		SUM(GM) as GM,
		IsPriorYear
	From @salesTable
	Where IsPriorYear IS NOT NULL
	Group By IsPriorYear 
) Total on s.IsPriorYear = Total.IsPriorYear
Inner Join 
(
	select parentCat.Id as CatId, parentCat.Name as CatName, SUM(Width * Facings) / 60 as LinealSpace
	from Articles ar
	Inner Join Categories cat on ar.CategoryId = cat.Id
	Inner Join Categories parentCat on parentCat.Id = cat.ParentId
	where (@CategoryId = 0 or parentCat.Id = @CategoryId)
	  and ar.RangeReviewId = @RangeReviewId
	  and ar.IsDeletedPerformance = 0
	group by parentCat.Id, parentCat.Name 			
) Spaces on Spaces.catId = s.CategoryId

Where (@CategoryId = 0 or CategoryId = @CategoryId) and s.IsPriorYear IS NOT NULL
Group by
	Title
	, CategoryId
	, Category
	, Total.Units
	, Total.Sales
	, Total.GM
	, Spaces.LinealSpace
	, s.IsPriorYear
OPTION(RECOMPILE);